How to Exclude Current or Partial Weeks

SQL Tutorial Home

If we have a table like:

create table sales ( sold_at  date,
		revenue  numeric(6,2)
);

The following date expression returns the starting point of the current week:

select subdate(sold_at, weekday(sold_at));

Then, if we want to show all the sales except those occurred during the current week, we can do:

select sold_at, revenue as weekly_revenue from sales
where sold_at < subdate(current_date, weekday(current_date)); -- exclude current week

Finally if we want data from all weeks, (avoiding data from partial weeks), for example if we want to show the weekly revenue of the last 4 weeks, excluding the current week, we can do:

select date_trunc('week', sold_at),sum(revenue) as weekly_revenue from sales
where sold_at < subdate(current_date, weekday(current_date)) -- exclude current week
-- include data from the previous 4 whole weeks
and sold_at > subdate(current_date, (weekday(current_date) + 28));

IN THIS PAGE